package com.hualan.dao.impl;
import com.hualan.dao.ShopGoodsDao;
import com.mchange.v2.c3p0.ComboPooledDataSource;

import javax.sql.DataSource;
import java.sql.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ShopGoodsDaoImpl implements ShopGoodsDao {
    private static DataSource ds= new ComboPooledDataSource();
    private static DataSource ds1= new ComboPooledDataSource();
    @Override
    public List<Map<String, Object>> showGoods() throws Exception {

        String sql = "SELECT * FROM panghudaojia.panghu_shop_goods";
        return query(sql);
    }

    public int allnum() throws SQLException {
        String sql1 = "SELECT count(*) FROM panghudaojia.panghu_shop_goods ";
        Connection con1 = ds1.getConnection();
        PreparedStatement preparedStatement1 = con1.prepareStatement(sql1);
        ResultSet resultSet1 = preparedStatement1.executeQuery();
        if (resultSet1.next()) {  // 先判断是否有下一行
            int totalCount = resultSet1.getInt(1);
            return totalCount;
        } else {
            // 处理结果集为空的情况，例如返回 0 或抛出异常
            return 0;
        }
    }

    @Override
    public List<Map<String, Object>> sousuo(int number, int size,String goods,String shop,String star,String end) throws Exception {

        String sql="SELECT g.*, s.shop_name, b.sales_volume FROM panghu_shop_goods g left JOIN panghu_shop s ON g.shop_id = s.shop_id left JOIN panghu_bulk_goods b ON g.goods_id = b.bulk_goods_id  ";
        String sql0 = "SELECT * FROM panghudaojia.panghu_shop_goods  ";
//        between '2024-8-9' and '2024-8-10'

        String goodsKeyword = goods;
        String shopKeyword = shop;
        String startime = convertDate(star);
        String endtime =convertDate(end);
        String dateKeyword ="";
        boolean hasConditions = false;

        if (!goodsKeyword.isEmpty()) {
            sql += "WHERE goods_name LIKE ? ";
            hasConditions = true;
        }
        if (!shopKeyword.isEmpty()) {
            if (hasConditions) {
                sql += "AND shop_name LIKE ? ";
            } else {
                sql += "WHERE shop_name LIKE ? ";
                hasConditions = true;
            }
        }
        if (startime!=null&&endtime!=null) {
            if (hasConditions) {
                sql += "AND g.add_time between ? and ?" ;
            } else {
                sql += "WHERE g.add_time between ? and ? ";
                hasConditions = true;
            }
        }
        if (!dateKeyword.isEmpty()) {
            if (hasConditions) {
                sql += "AND publish_date = ? ";
            } else {
                sql += "WHERE publish_date = ? ";
            }
        }
//                sql+="LIMIT ?,?";


        List<Map<String, Object>> list = new ArrayList<Map<String,Object>>();
        Connection con = ds.getConnection();
        PreparedStatement preparedStatement = con.prepareStatement(sql);

        int offset = (number - 1) * size;

        int parameterIndex = 1;
        if (!goodsKeyword.isEmpty()) {
            preparedStatement.setString(parameterIndex++, "%" + goodsKeyword + "%");
            System.out.println("%" + goodsKeyword + "%");
        }
        if (!shopKeyword.isEmpty()) {
            preparedStatement.setString(parameterIndex++, "%" + shopKeyword + "%");
        }
        if (startime!=null) {
            preparedStatement.setString(parameterIndex++, startime);
            System.out.println(startime);
        }
        if (endtime!=null) {
            preparedStatement.setString(parameterIndex++, endtime);
        }
        if (!dateKeyword.isEmpty()) {
            preparedStatement.setString(parameterIndex, dateKeyword);
        }
//        preparedStatement.setInt(parameterIndex++, offset);
//        preparedStatement.setInt(parameterIndex, size);



        System.out.println(sql);

        ResultSet resultSet = preparedStatement.executeQuery();
        //获取结果集元数据
        ResultSetMetaData rsmd = resultSet.getMetaData();
        //获取查询的列数
        int columnCount = rsmd.getColumnCount();
        //创建一个String数组,存放列名
        String[] columnNames = new String[columnCount];
        for(int i = 1; i <= columnCount; i++) {
            //通过列号 获取列名
            String columnLabel = rsmd.getColumnLabel(i);
            columnNames[i-1] = columnLabel;
        }
        //遍历结果集
        while(resultSet.next()) {
            Map<String, Object> map = new HashMap<String, Object>();
            //遍历每一列
            for(int i = 0; i < columnNames.length; i++) {
                //获取列名
                String columnName = columnNames[i];
                //通过列名获取值
                Object value = resultSet.getObject(columnName);
                //把列名和值存入map中
//                    String columnName1=underlineToCamel(columnName);
                map.put(columnName, value);
            }
            list.add(map);
        }

        return list;

    }



    @Override
    public List<Map<String, Object>> fenyegoodslist(int number,int size) throws Exception {

            //多表sql改装21.54
            //数据完整sql
            String sql00="SELECT g.*, s.shop_name, b.sales_volume FROM panghu_shop_goods g JOIN panghu_shop s ON g.shop_id = s.shop_id JOIN panghu_bulk_goods b ON g.goods_id = b.bulk_goods_id LIMIT ?,?";
            //左连接sql
            String sql="SELECT g.*, s.shop_name, b.sales_volume FROM panghu_shop_goods g left JOIN panghu_shop s ON g.shop_id = s.shop_id left JOIN panghu_bulk_goods b ON g.goods_id = b.bulk_goods_id LIMIT ?,?";

            String sql0 = "SELECT * FROM panghudaojia.panghu_shop_goods LIMIT ?,?";
            List<Map<String, Object>> list = new ArrayList<Map<String,Object>>();
            Connection con = ds.getConnection();
            PreparedStatement preparedStatement = con.prepareStatement(sql);
            int offset = (number - 1) * size;
            preparedStatement.setInt(1, offset);
            preparedStatement.setInt(2, size);

        ResultSet resultSet = preparedStatement.executeQuery();

        //获取结果集元数据
        ResultSetMetaData rsmd = resultSet.getMetaData();
        //获取查询的列数
        int columnCount = rsmd.getColumnCount();
        //创建一个String数组,存放列名
        String[] columnNames = new String[columnCount];
        for(int i = 1; i <= columnCount; i++) {
            //通过列号 获取列名
            String columnLabel = rsmd.getColumnLabel(i);
            columnNames[i-1] = columnLabel;
        }
        //遍历结果集
        while(resultSet.next()) {
            Map<String, Object> map = new HashMap<String, Object>();
            //遍历每一列
            for(int i = 0; i < columnNames.length; i++) {
                //获取列名
                String columnName = columnNames[i];
                //通过列名获取值
                Object value = resultSet.getObject(columnName);
                //把列名和值存入map中
//                    String columnName1=underlineToCamel(columnName);
                map.put(columnName, value);
            }
            list.add(map);
        }

        return list;

    }

    @Override
    public List<Map<String, Object>> update(int id,int status) {
        String sql ="UPDATE panghudaojia.panghu_shop_goods SET goods_status = ? WHERE goods_id = ?";
        update1(sql,id,status);
        return null;
    }

    @Override
    public List<Map<String, Object>> statuslist(String i) throws Exception {



        String sql0 = "SELECT * FROM panghudaojia.panghu_shop_goods where goods_status ="+i;
        String sql = "SELECT g.*, s.shop_name, b.sales_volume FROM panghu_shop_goods g left JOIN panghu_shop s ON g.shop_id = s.shop_id left JOIN panghu_bulk_goods b ON g.goods_id = b.bulk_goods_id where goods_status = "+i;
        System.out.println(sql);
        return query(sql);
    }

    public static boolean update1(String sql, int id, int status){
        try(Connection con = ds.getConnection();
            PreparedStatement stmt = con.prepareStatement(sql);){
            int parameter = status;
            stmt.setInt(1,parameter);
            stmt.setInt(2,id);
            int rowsAffected = stmt.executeUpdate();
            if (rowsAffected > 0) {
                System.out.println("更新成功，影响的行数: " + rowsAffected);
                return true;
            } else {
                System.out.println("更新失败，没有行受到影响。");
                return false;
            }

        }catch(Exception e) {
            e.printStackTrace();
        }
        return true;
    }
    public static List<Map<String, Object>> query(String sql, Object...parameters){

        List<Map<String, Object>> list = new ArrayList<Map<String,Object>>();
        try(Connection con = ds.getConnection();
            PreparedStatement stmt = con.prepareStatement(sql);){
            //给sql语句中的?赋值
            for(int i = 0; i < parameters.length; i++) {
                stmt.setObject(i + 1, parameters[i]);
            }
            //执行查询,获取结果集
            ResultSet resultSet = stmt.executeQuery();
            //获取结果集元数据
            ResultSetMetaData rsmd = resultSet.getMetaData();
            //获取查询的列数
            int columnCount = rsmd.getColumnCount();
            //创建一个String数组,存放列名
            String[] columnNames = new String[columnCount];
            for(int i = 1; i <= columnCount; i++) {
                //通过列号 获取列名
                String columnLabel = rsmd.getColumnLabel(i);
                columnNames[i-1] = columnLabel;
            }
            //遍历结果集
            while(resultSet.next()) {
                Map<String, Object> map = new HashMap<String, Object>();
                //遍历每一列
                for(int i = 0; i < columnNames.length; i++) {
                    //获取列名
                    String columnName = columnNames[i];
                    //通过列名获取值
                    Object value = resultSet.getObject(columnName);
                    //把列名和值存入map中
//                    String columnName1=underlineToCamel(columnName);
                    map.put(columnName, value);
                }
                list.add(map);
            }
        }catch(Exception e) {
            e.printStackTrace();
        }
        return list;
    }
    public static String convertDate(String inputDate) {
        String dateString = inputDate;
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        try {
            java.util.Date date = format.parse(dateString);
            String s =format.format(date);
            return s;

        } catch (ParseException e) {
            e.printStackTrace();
            return null;
        }

    }


}
